SQL Editor

The SQL editor allows to send queries directly to the connected CData Virtuality Server.

The upper pane of the editor allows for entering queries; in the lower pane results of the query are displayed.

Keyword shortcuts and actions

Opening and saving a script

The SQL editor allows to save the current script to a file, as well as to open a saved script (Open script file and Save script file buttons). If an existing file is open in an SQL Editor, it can be saved under a different name with the Save as button. Please note, only the UTF-8 character encoding is supported.

Issueing queries

To issue a query type it into the SQL editor.

You can limit the number of results by setting the Limit Rows value in the filter line. You can reuse one of the previously entered queries by selecting it in the quick history popup or in the Advanced History Dialog .

CSV and XML Query Builders

The SQL Editor includes wizards to facilitate the generation of queries that retrieve data from CSV or XML files, either local or remote (from a web service). To open the corresponding wizard, simply click on the CSV query builder or the XML query builder buttons, located in the toolbar. Please note that these wizards require a data source of type "File" or "Web Service" to be configured in the Server.

Query plan

The SQL editor is able to build the execution plan for the current query without executing it. To use it, simply press the Show query plan button. The current query is either the selected text or the query under the cursor if nothing selected, as delimited by the statement separator.

CLOB limitations

Please note that the SQL Editor result pane displays the preview of the value limited by 250 characters. A tooltip can be displayed on a cell that shows first 32 Kilobytes of data. It is activated by a double click or on hover, depending on the configured CData Virtuality Studio preferences.

Supported SQL query language

CData Virtuality Server supports, among others, select queries, insert queries, stored procedure language and create / alter statements. For a full list of supported SQL features please see the reference manual.

Following are examples for valid SQL editor queries.

Please note, the default statement separator in the SQL Editor is ;; (double semicolon). It can be changed in the Preferences.
Anonymous stored procedures 
BEGIN
    SELECT * FROM adventureworks.address;
END
BEGIN
    call SYSADMIN.createOptimization('adventureworks.contact');
END
BEGIN
DECLARE date startdate='2012-01-01';
DECLARE date enddate='2012-02-04';
DECLARE date idate;
idate=startdate;
CREATE LOCAL TEMPORARY TABLE #x(xdate date);  
  WHILE (idate<=enddate)  
  BEGIN	
    INSERT INTO #x(xdate) VALUES (idate);
	idate=timestampadd(SQL_TSI_DAY,1,idate);
  END
SELECT * from #x;
END
Stored procedures 
CREATE VIRTUAL PROCEDURE views.dateaxis(IN startdate date,IN enddate date) returns (xdate date) as 
BEGIN
DECLARE date idate;
idate=startdate;
CREATE LOCAL TEMPORARY TABLE #x(xdate date);
  WHILE (idate<=enddate)
  BEGIN
  	INSERT INTO #x(xdate) VALUES (idate);
 	idate=timestampadd(SQL_TSI_DAY,1,idate);
  END
SELECT * from #x;
END;;



ALTER PROCEDURE views.dateaxis() returns (xdate date) as 
BEGIN
  SELECT current_date;
END;;
Views 
CREATE VIEW views.countryregion AS SELECT * FROM adventureworks.countryregion;;
CREATE VIEW views.creditcard AS SELECT * FROM adventureworks.creditcard;;
CREATE VIEW views.countryregion AS SELECT * FROM adventureworks.countryregion;;
CREATE VIEW views.creditcard AS SELECT * FROM adventureworks.creditcard;;
ALTER VIEW views.creditcard AS SELECT * from (SELECT * FROM adventureworks.creditcard) a;;
CREATE VIEW views.countryregion AS SELECT * FROM adventureworks.countryregion;
DROP VIEW views.countryregion;
Select 
Select * from adventureworks.address;;
Create table 
create table dwh.testtable(a integer);;
drop table dwh.testtable;;
Insert into / Select into 
create table dwh.testtable(a integer);;
insert into dwh.testtable select 1;;
select 1 into dwh.testtable;;
select * from dwh.testtable;
Explain 
explain SELECT * FROM adventureworks_part_one.addresstype

Reference manual pages: